﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace WFA_BANHANG.ThuVien
{
    public class ADONET
    {
        #region(GobalVariable)
        private string _strConnection;
        private SqlDataAdapter _sqlda;
        private SqlDataReader _sqldr;
        private SqlCommand _sqlcmd;
        private SqlConnection _sqlcon;
        private SqlTransaction _sqltran;
        #endregion

        public ADONET() {

            this._strConnection = "SERVER=112.78.6.27;DATABASE=anhlan_SaleManager;UID=anhlan_lannh;PWD=lannh***14782;";
        }
        
        public ADONET(string strConnection) {

            this._strConnection = strConnection;
        }
        
        //method return DataSet
        public DataSet BindToDataSet(string query) {

            try {
                
                _sqlda = new SqlDataAdapter(query,_strConnection);
                DataSet ds = new DataSet();
                _sqlda.Fill(ds);

                return ds;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message,"Thông báo"); return null; }
        }

        //method Insert, Update & Delete
        public Int32 SetDataInputOutputDatabase(string sp, object[] paramName, object[] paramValue)
        {
            _sqlcon = new SqlConnection(_strConnection);
            _sqlcon.Open();           
            _sqlcmd = _sqlcon.CreateCommand();
            _sqltran = _sqlcon.BeginTransaction();
            _sqlcmd.Transaction = _sqltran;

            Int32 result = 0;
            try
            {
                _sqlcmd.CommandType = CommandType.StoredProcedure;
                _sqlcmd.CommandText = sp;

                for (int i = 0; i < paramName.Length; i++)
                {
                    SqlParameter[] sqlpr = new SqlParameter[paramName.Length];
                    sqlpr[i] = new SqlParameter(paramName[i].ToString(), paramValue[i]);
                    _sqlcmd.Parameters.Add(sqlpr[i]);
                }

                 result = _sqlcmd.ExecuteNonQuery();
                _sqltran.Commit();
                
                return result;

            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);

                try
                {
                    _sqltran.Rollback();
                }
                catch { }

                return result;
            }

            finally
            {
                if (_sqlcon != null)
                {
                    _sqlcon.Close();
                }

                _sqlcmd.Parameters.Clear();
                _sqlcmd.Dispose();
            }

        }//the end method Insert, Update & Delete

    }//the end class
}//the end namespace
